package com.fsd.core.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Access操作类
 * @author lumingbao
 */
public class AccessUtil {
	
	/**
	 * 创建Access连接
	 * @param mdburl
	 * @return
	 * @throws Exception
	 */
	public static Connection createConnect(String mdburl) throws Exception{
		Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
		String url = "jdbc:Access:///"+ mdburl;
		Connection conn = DriverManager.getConnection(url, "", "");
		return conn;
	}
	
	/**
	 * 执行Sql语句
	 * @param sql
	 * @param values
	 * @return
	 */
	public static void executeSql(Connection conn, String sql) throws Exception{
		Statement sta = conn.createStatement();
		sta.execute(sql);
	}
	
	/**
	 * 执行Sql查询
	 * @param conn
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> queryBySql(Connection conn, String sql) throws Exception{
		Statement sta = conn.createStatement();
		ResultSet set = sta.executeQuery(sql);
		if(set != null){
			return resultSetToList(set);
		}else{
			return null;
		}
	}
	
	/**
	 * ResultSet转List
	 * @param rs
	 * @return
	 * @throws Exception
	 */
	private static List<Map<String, Object>> resultSetToList(ResultSet rs) throws Exception{   
        if (rs == null){
            return Collections.emptyList();
        }
        ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
        int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Map<String, Object> rowData = new HashMap<String, Object>();
        while (rs.next()) {
	        rowData = new HashMap<String, Object>(columnCount);
	        for (int i = 1; i <= columnCount; i++) {
	        	rowData.put(md.getColumnName(i), rs.getObject(i));
	        }
	        list.add(rowData);
        }
        return list;
	}
}
